import xlwt

from quote_folder.Db_connect import DB_connect


class GetData():
    def __init__(self):
        self.bigdata_con = DB_connect().online_big_data()
        self.bigdata_cur = self.bigdata_con.cursor()
        self.open = DB_connect().mysql_mykh()
        self.open_cur = self.open.cursor()

    def run(self):
        sqh_li = self.get_hubei()
        update_li = self.get_alreay_fee(sqh_li)
        # print("开始制作表格！")
        # self.save_json_dict(update_li)

    def get_hubei(self):
        total_item = []
        sql = "SELECT application_number FROM `big_data`.`ccy_paid` WHERE `paid_date` > '2022-01-01' GROUP BY application_number"
        self.bigdata_cur.execute(sql)
        retrundata = self.bigdata_cur.fetchall()
        sqh_li = [item[0] for item in retrundata]
        return sqh_li

    def get_alreay_fee(self, items):
        info_li = []
        for sqh in items:
            item = {}
            sqh = "ZL" + sqh
            sql = f"SELECT application_number, fm_name, patent_user FROM xhhg_pantent_status WHERE application_number = '{sqh}'"
            # SQL = f"SELECT application_number, money_type, money_number, paid_date FROM `big_data`.`ccy_paid` WHERE `application_number` = '{sqh}' AND `paid_date` > '2022-01-01'"
            # sql = f"select money_type from ccy_paid WHERE `application_number` = '{sqh}' AND `paid_date` < '2022-01-01'"
            self.bigdata_cur.execute(sql)
            aa = self.bigdata_cur.fetchall()
            try:
                if '湖北工程学院' in aa[0][2] or "孝感学院" in aa[0][2]:
                    item['sqh'] = sqh
                    item['name'] = aa[0][1]
                    item['patent_user'] = aa[0][2]
                    print(item)
                    info_li.append(item)
            except:
                item['sqh'] = sqh
                item['name'] = ""
                item['patent_user'] = ""

        print(len(info_li), info_li)

        #         item['flag'] = "1"
        #     self.bigdata_cur.execute(SQL)
        #     data = self.bigdata_cur.fetchall()
        #     if len(data) > 1:
        #         print("------------------------" * 3)
        #         print(data)
        #         print("------------------------" * 3)
        #         for i in range(len(data)):
        #             item['money'] += int(float(data[i][2]))
        #             type_li.append(data[i][1])
        #             item['type'] = "+".join(type_li)
        #             item['pay_date'] = data
        #     if len(data) == 1:
        #         item['money'] = int(float(data[0][2]))
        #         item['type'] = data[0][1]
        #     print(item)
        #     Item.append(item)
        # return Item

    def save_json_dict(self, update_li):
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet("sheet1")
        sheet.write(0, 0, '序号')
        sheet.write(0, 1, '申请号')
        sheet.write(0, 2, '专利名字')
        sheet.write(0, 3, '专利权人')
        sheet.write(0, 4, '费用类型')
        sheet.write(0, 5, '费用金额')
        sheet.write(0, 6, "标记")
        if len(update_li) > 0:
            for i in range(len(update_li)):
                sheet.write(i + 1, 0, i + 1)
                sheet.write(i + 1, 1, update_li[i]['sqh'])
                sheet.write(i + 1, 2, update_li[i]['name'])
                sheet.write(i + 1, 3, update_li[i]['patent_user'])
                sheet.write(i + 1, 4, update_li[i]['type'])
                sheet.write(i + 1, 5, update_li[i]['money'])
                sheet.write(i + 1, 6, update_li[i]['flag'])
            workbook.save('./BBBBB.xls')


    def HB(self):
        sql1 = "SELECT * FROM `mykhadmin`.`orders` WHERE `patent_user` LIKE '%孝感学院%'"
        self.open_cur.execute(sql1)
        data = self.open_cur.fetchall()
        for i in data:
            id = i[0]
            # print(id)
            sql2 = f"SELECT application_number, organization FROM `mykhadmin`.`finals` WHERE `order_id` = '{id}'"
            self.open_cur.execute(sql2)
            ddd = self.open_cur.fetchall()
            if ddd[0][1] == "武汉信合红谷知识产权代理事务所（特殊普通合伙）":
                print(ddd[0][0])

    def FE(self):
        with open('sqh.txt', "r") as fr:
            for i in fr:
                sql = f"SELECT * FROM `big_data`.`ccy_paid` WHERE `application_number` LIKE '%{i.strip()}%'"
                self.bigdata_cur.execute(sql)
                data = self.bigdata_cur.fetchall()
                try:
                    print(data[-1][-2])
                except:
                    print("")


if __name__ == '__main__':
    # GetData().run()
    # GetData().HB()
    GetData().FE()
